MySQL パフォーマンスチューニング

概要

MySQLの性能・パフォーマンスを改善する方法としては、以下4つほどに分かれる。

1. SQLを改善する

2. インデックスを追加する

3. DBパラメータを変更する

4. その他のDB以外の箇所を変更する

4は、リソース増強やキャッシュ層追加などが当てはまってくる。

性能（パフォーマンス）とは

まず性能が出てないってどう言うこと？ってなった時に一番に見るべきは...

レイテンシとは、クライアントからの要求に対して平均どれくらいの速さで返してるか？という話。

スループットとは、クライアントからの要求を平均どれくらいの量捌いてるか？という話。

この2つの値が、自分が想定してる目標でなかった場合は、性能がダメてことを意味する。

ex：

DBからの平均レイテンシを200ms以下にしたい

DBの1秒平均処理量（スループット）を200mb/s以上にしたい

性能要因①：リソース

CPU、メモリ、ネットワーク、IOPS限界、ストレージサイズ、スループット限界。

一般的に、DBのリソースとして見られるのは上記あたりかなぁと。

DBの性能を見る際、上記どれかのメトリクスが限界に近い場合は、リソースを増強した方がいいかも。

性能要因②：DB（クエリチューニング）

明らかに非効率的なクエリを実行してると、無駄処理が多くなって無駄にスループット、レイテンシを大きくしてしまう。

なので、非効率的なクエリを改善することで、リソース使用量の無駄を無くしていこう

性能要因③：DB（パラメータチューニング）

クエリの処理が遅くなってる要因として、パラメータ設定も挙げられる。

バッファのサイズが小さいために、無駄にI/Oが発生してるとかそういうの。

言うなれば、ここもDB版リソース設定みたいなもんなのかな？

「この設定の値がボトルネックになってるから値を変更しよう」的な感じで進めていくことになると思うし。

リソース増強のやり方と一緒かも。

まあ、チューニングなんやからそうよな。

hr.icon

hr.icon

パフォーマンスチューニングとは...要するに「手段は問わない、速くしろ」てこと

アプローチとして「SQLを書き換える」「インデックスを追加する」「パラメータを変更する」「それ以外の何か」が挙げられる。

SQLチューニングは決まれば（つまり元の状態がよほど悪いということでもありますが）100倍以上の性能改善を叩き出せますが、1回のチューニングで影響のある範囲は広くありません。特定のクエリーに対するチューニングなので、別のクエリーが速くなることはほぼないのです。

パラメーターを1回変更してしまえば（それが決まっていれば）、ほぼ全てのクエリーに対して透過的に（SQLの書き換えなしに）影響を及ぼすことが期待できます。その代わりSQLチューニングほどの性能改善はなく、せいぜい数倍程度です（大概の場合は数倍どころか数%ではないでしょうか。数倍出ればよほど今までの値が悪かったのだろう、ということになります）。大きな効果が見込めるパラメーターは積極的にチューニングしていくモチベーションがありますが、ある程度のところまで行くとパラメーターを細かくチューニングするよりもSQLチューニングに時間をかけた方が効率が良くなります（秘伝のタレに例えられるmy.cnfは、この「ある程度のところ」が詰まっていることが多いです）。

でもこれって、逆に他の場所に悪影響が出る可能性もあるよな？範囲がでかいから

あと、行くとこまで行ったら、効果増が小さくなるから、そこからはクエリチューニングに切り替えた方がいいって。

それ以外の何か

スケールアップやスケールアウト（シャーディング）、キャッシュ用ミドルウェアの追加（つまり、その処理にMySQLを直接使わなくする）、RDBMSを入れ替える（例：MariaDBに変更など）、カーネルパラメーターのチューニングなどを想定しています。

まあ、リソース増強とかはやりやすい手よな。

パフォーマンスチューニングに「銀の弾丸」はない

本連載ではチューニングそのものの方法については詳しく説明しません。それは見出しの通り「銀の弾丸」などはなく、MySQLのパフォーマンスチューニングは計測と改善を繰り返し行っていくべきものだからです。そのため、特定のケースにマッチする改善の手法よりも、繰り返し使われる計測の手法にフォーカスを当てて説明していきます。

sort_buffer_size

インデックスがないカラムの ORDER BY,GROUP BY操作を高速化するために使われるバッファサイズ

ほお、インデックスがないカラムが関連してる場合は、ここを見ればよさそうだな。

join_buffer_size

インデックス無しjoin(結合)、インデックスを付与するとき等で使われる最小バッファサイズ

どう言うタイミングで拡張するのか、よくわからん。

max_connections

サーバが許容可能なコネクションの数

デフォルト(設定なし)の場合は151

通常デフォルト値で問題ないが接続数が多い場合はそれに応じて増やす

意外と気づかない

thread_cache_size

スレッドをキャッシュする数

応答時間が長い場合に大きくすると改善する可能性があるが程々に

そんな効果ないと思うなぁ。やってみてのお楽しみではあるが

query_cache_type

query_cache_size

innodb_buffer_pool_size

InnoDBのテーブルとインデックスのバッファサイズ

（テーブルとインデックスをメモリにキャッシュするサイズ）

大きくするとディスクI/Oを減らし、処理を改善できる可能性がある

デフォルト128M、最大でマシン合計メモリーの80%を割り当てることもあります

大きくし過ぎるとOSのページングにより余計に遅くなる為、

搭載メモリ量やOSの他プロセスのメモリ消費量を配慮して設定する必要がある

ここは大きければ大きいほど、disk ioが下がって性能向上につながる。

ただし、他プロセスの存在を無視して、ページングとか発生させると遅くなるだろう。

disk ioが重くのしかかってくる

innodb_log_file_size

ロググループ内の各ログファイルのサイズ

innodb_buffer_pool_sizeの25%程度に設定する

ここを大きくすることで、ダーティページのフラッシュ頻度が減るだろう。

ここ小さいと、すぐファイルが満杯になって、フラッシュしなきゃいかんくなる。（はず）

sort_buffer_size はソート用のメモリー領域を指定するパラメータ。ソート処理実行時にこのメモリー領域サイズを超えるソート処理はディスク上のファイルを利用して実行、つまり、ディスクIOが発生する。そのためパフォーマンスの低下に繋がる。

そのため、ファイルソートが発生している場合は、sort_buffer_sizeを増やす必要がある可能性あり。

ファイルソートの発生有無は、Sort_merge_passesが発生しているで確認できる

MySQL にはクエリの最中に内部的に一時テーブルを作成する動作がある。

内部一時テーブルが最初にインメモリーテーブルとして作成されたが、これが大きくなりすぎた場合、MySQL はこれを自動的にディスク上のテーブルに変換する。インメモリー一時テーブルの最大サイズは、tmp_table_size と max_heap_table_size の最小値となり、これは、CREATE TABLE によって明示的に作成された MEMORY テーブルと異なる。

頻出問題ねこれ

max_table_sizeってのも、その一時テーブルサイズに対する制約ってことかな

一時内部テーブルの仕組みは以下のため、以下２種類の値を比較することで、どの程度の割合でディスクIOが発生しているか分かる。

サーバーは内部一時テーブル (メモリー内またはディスク上のいずれか) を作成すると、Created_tmp_tables ステータス変数を増分する

サーバーはディスク上にテーブルを作成する (内部で、またはインメモリーテーブルを変換して) 場合、Created_tmp_disk_tables ステータス変数を増分する

innodb_log_buffer_size

コミットされていないトランザクションのためのバッファのサイズ。

デフォルト値(1MB)は多くの場合で適量かもしれないが、大きなblobやテキストのカラムを扱うトランザクションを使っている場合、すぐにバッファはいっぱいになり、余計なI/Oを引き起こすことになる。SHOW ENGINE INNODB STATUS の Innodb_log_waits を確認してそれが 0 でなければ、innodb_log_buffer_sizeを増やすこと。

* Innodb_log_waits = ログバッファーが小さすぎるため、一度領域をディスクに対してフラッシュするために待機が必要だった回数。

redo_logのバッファ機構よね、これすぐいっぱいになるようだと、何回もfile ioが起きるわな。

まあ、redo logの場合、シーケンシャルアクセスだから、比較的早いって言う感じなんだろうけど。

1. バッファを増やす、または減らす

innodb_buffer_pool_size・・・InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要だ。

ただ、5 ~ 10%多くメモリ使用するってどう言うこと？それは約束と違うだろうが。

そんな感じだと、OS管理プロセスが利用するメモリを食べちゃうでしょうが。

key_buffer_size・・・MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。

まあ、myisamを使うことはないやろうからほぼ無視で。

sort_buffer_size・・・ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

1Mはなかなか大きいと思うがね

同時接続数が1000とかあったら1GBのメモリが常時食われるんかい。デカすぎね？

read_buffer_size・・・全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。

全件スキャンか。そもそもあんまそう言うことしちゃダメよっていう。

read_rnd_buffer_size・・・ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

まあ、うん。おk。

バッファは増やせば増やすほどいいかと言えばそうではない。メモリの割り当てがオーバーヘッドになるので、無駄に大きくし過ぎることは禁物である。また、バッファを増やしすぎたためにスワップが発生するとパフォーマンスが悲惨なことになるのでくれぐれも空きメモリ容量には注意しよう。

2. 高速なディスクを利用する

MySQLだけに限った話ではないが、RDBMSのボトルネックは99.99999%がディスクI/Oである。特にディスクのシークタイムによる待ち時間が大きい。理想的にはバッテリーバックアップ付きのRAID装置を利用するのがいい。最近はRAID装置に匹敵するほど高速なSSDが出てきているので楽しみである。

RDBMSのボトルネックは99.99999%がディスクI/O

結局、ここに行きたつんかな

後は「待ち」もあるけど、それもディスクI/Oってこと？そう言うわけでもないか

前述のようにバッファを大きくするとディスクI/Oの回数や量が減るので、必ずしも高速なディスクが性能を向上させるというわけではないが、データサイズが大きくてバッファに収まりきらない場合などにはどうしてもI/Oが大量に発生してしまう。そんな時は高速なディスク装置を利用するといい。

バッファに収まり切らない！！ってすげぇサイズやな

3. クエリを最適化する

実は最も大事なのがクエリの最適化である。いくら他の部分を最適化したところで、毎回全件スキャンが発生していた のでは話にならない。適切にインデックスを使ったり、サブクエリをJOINに書き換えたりすることで、フェッチしないといけない行数ができるだけ少なくなるようにクエリを書きかえよう。クエリを最適化するには、まずEXPLAINで実行計画をチェックしよう。EXPLAINの見方についてはいずれ解説しようと思う。

どういう実行計画してんねんっていう。

やはりクエリチューニングの効果は絶大なのですね

流石に効率悪い？

全件フェッチってのがよくないんかな。

使わないデータをバッファに持ってくることになるからてきな？

クエリを手当たり次第チューニングしていてはいくら時間があっても足りないだろう。問題のあるクエリだけをチューニングするべきであるが、そのようなクエリを見付けるにはスロークエリログや商用のクエリアナライザを用いると効果的である。

4. テーブルを最適化する

基本中の基本は、適切なデータタイプを使うということである。できるだけカラムサイズが小さくなるようなデータタイプを選ぼう。数値をVARCHAR(桁数)などのデータタイプで格納しているのをたまに見かけるが、これは誤りである。INTまたはBIGINTなどを利用したほうがずっとデータサイズが小さくなるし高速である。

心得た

また、適切なカラムに対してインデックスをつけるのも重要である。どのカラムにインデックスをつけるかは、クエリのパターンに因る。インデックスが多すぎると更新時のオーバーヘッドが大きくなるだけでなく、インデックスツリーを格納するためのデータ容量が増えてしまうので、インデックスのつけすぎには注意しよう。たまに全てのカラムにインデックスがついているテーブルを見かけるが、そのようなテーブル設計は誤りである。クエリのパターンによっては、マルチカラムインデックスやパーティショニングが必要になるなどいろいろと工夫が必要になる。

まあ、基本インデックス：カラム＝１：１になる。

そして、インデックスは検索しやすいようそのカラム専用のツリーが存在することになる。

セカンダリインデックスってのが作られる。この構造がインデックスがついたカラム1つ1つに存在することになるから。その分データ量も増える。

また、更新時は特にインデックスツリー全部更新しないといけなくなって、性能はどん底に落ちるだろう。

カラム数がが多くなりすぎたら、まずは正規化できるかどうかを検討してみて欲しい。DWH用途などでは逆に非正規化すると性能が向上する場合がある。

5. 目的に合ったストレージエンジンを選択する

これはMySQLの醍醐味である。ストレージエンジンはそれぞれ性能特性がまったく違うので、目的に合ったストレージエンジンを選択すると劇的に性能が向上する場合がある。例えば、OLTPではInnoDB、参照系が多い場合はMyISAM、ログ目的であればARCHIVE、リアルタイム並列処理であればNDBCLUSTERなど。他にもSun/MySQL以外のサードベンダーやコミュニティからリリースされているストレージエンジン（SPIDER、PBXT、XtraDB、Q4M、Infobright、Kickfireなど）もあるので、目的に合わせて色々検討してみるといいだろう。

まあ、必要になったら調べるでいいだろう

6. レプリケーションで負荷分散する

MySQLほどお手軽に、そして安価にレプリケーションを利用出来るRDBMSは他にないだろう。レプリケーションを用いてたくさんのスレーブへ参照系の処理を負荷分散するテクニックは、Webサイトなどで頻繁に利用されているテクニックである。参照系の負荷分散を行う場合だけでなく、例えばOLTPのデータを元にBIなどの処理を毎日行う場合などにも有効である。スレーブ上でBIを行えば、マスター上のOLTP系の処理に影響を与えることがない。

参照系の処理負荷分散くらいなら非同期で良さそうね。ビジネス特性にもよるけど

7. ストアドプログラムを多用しない

残念ながら、MySQLはストアドプロシージャ、ストアドファンクション、トリガなどの性能はあまりよくない。出来るだけそれらを利用せずに、ロジックをアプリケーション側に持っていくといいだろう。

8. ファイルシステムをチューニングする

Linuxであればデフォルトはext3（そろそろext4になっていくだろうか？）であるが、ext3ではなくXFSを利用すると性能が向上する場合がある。また、I/Oスケジューラを変更することで、同じext3であっても性能特性が変化する。SolarisではUFS、ZFS、QFSなどの利用を検討するといいだろう。WindowsならNTFS以外にあまり選択肢はないが、MyISAMの場合はLargeSystemCacheを有効にするなどのチューニングが必要である。

9. コネクションプールを利用する

アプリケーションがDB操作が必要なときに都度MySQLサーバへ接続していたのでは、接続のためのオーバーヘッドが無視出来なくなる。そんなときはコネクションプールを利用するといい。

10. ベンチマークする

どんなチューニングでも、実際に効果があるかどうかは測定してみるまで分からない。また、あるアプリケーションで効果があるチューニングでも、他のアプリケーションの負荷パターンでは逆効果になってしまうということは多々ある。なので、アプリケーションの負荷を擬似的に作り出してチューニングの効果を測定することはとても重要なのである。

頑張ればできるんか...うん。

復元しつつ、実行されるSQL文と量を分析して、ひたすら実行って感じか？それもむずい気がするが。

read_buffer_size

テーブルフルスキャンをする際に使われるバッファサイズ。

そもそもテーブルフルスキャンをするようなクエリをあまり発行すべきではないので基本小さ目でよいと考える。

read_rnd_buffer_size

join_buffer_size

sort_buffer_size

sort_buffer_size はソート用のメモリー領域を指定するパラメータ。ソート処理実行時にこのメモリー領域サイズを超えるソート処理はディスク上のファイルを利用して実行、つまり、ディスクIOが発生する。そのためパフォーマンスの低下に繋がる。

そのため、ファイルソートが発生している場合は、sort_buffer_sizeを増やす必要がある可能性あり。

ファイルソートの発生有無は、Sort_merge_passesが発生しているで確認できる

やはりファイルも使ってソートするのね

3.内部一時テーブルに関するメモリチューニン

MySQL にはクエリの最中に内部的に一時テーブルを作成する動作がある。

いずれにせよ、ディスク上に一時テーブル作られると性能悪化しますわな

4.バッファプール用メモリのチューニング

一般的なデータベースでは、バッファプールというテーブルデータとインデックスを乗せるメモリー領域が存在する。

バッファプールヒット率が高い値がなるべく 100% になるようにする。

バッファプールヒット率 = 1-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)x100(%)

「Threads_created」がめっちゃ多いなら、thread_cache_sizeを大きくするのもあり

9.テーブル/インデックス再編成

InnoDB では、更新を繰り返していると、断片化（フラグメンテーション）という現象が発生する。

フラグメンテーションが発生すると、本来読み取らなくて良い場所まで無駄に読み取る形になるため、クエリ処理が遅くなる可能性がある。

例えば、極端な例でいうと10000000行あるテーブルの内、5000000行を delete した状態だと、データとしては 5000000 行しかない一方でテーブルが占有している領域としては 10000000 行使っている状態になる

以下の data_free 列が割り当てられているが、使用されていないバイト数。この値が高いほどフラグメンテーション率が高いのでデフラグの必要性がある。

これが起きると、本来読まなくていい領域まで読むことで、クエリ処理が遅くなると...なるほど

２．１　データ型の見直し

テーブルに設定したデータ型と入力したデータのデータ型が異なっていた場合、変更可能であればエラーとならずに暗黙の型変換が行われます。便利な機能のように思えますが、この型変換にかかる時間を回避するためにも、入力されるデータの型をきちんと洗い出して設定することが重要となります。

ほい

２．２　インデックスの追加と削除

頻繁に発行するクエリの実行計画を調査し適したインデックスを追加することで、劇的な速度向上が期待できます。

さらに誤ったインデックスが貼られてしまっていたら、削除することで速度が向上する場合もあります。

インデックスの追加方法と実行計画の取得について、詳しくは次の記事をご参照下さい。

ほい

２．３　SQLの見直し

これは単純で、無駄なループをなくすなどの処理を見直したり、実行が遅い命令を早い命令に置き換えるなどで対応します。

例えば「UNION」は２つのテーブルを結合する機能ですが、重複行削除のための処理が行われるためその分時間がかかります。重複しない、または重複してもよいという場合は積極的に「UNION ALL」を使用することで、速度の向上が期待できます。

ほか、SELECT時に「*（ワイルドカード）」を使用すると不要な列まで取得することになったり、また全列を取得したい場合でも「*」を項目名に置き換える処理が内部で行われるため、その分時間がかかってしまいます。そのため必要な項目名を一つずつ指定することで、速度の向上が期待できます。同じような現象として、列番号を指定すると項目名への読み替えを内部で行う処理が追加されるため、こちらも避けるのが無難です。

２．４　ストアドプロシージャの削除

コールするだけで使用できるストアドプロシージャですが、実は普通にクエリを発行するよりも実行に時間がかかる場合があります。処理に時間がかかっているプロシージャを見つけたら、プロシージャを使用しない場合と実行時間の比較を行ってみて下さい。行っている内容が全く同じでも、実行計画が異なる場合があるようです。

はい

２．５　非正規化で処理速度が向上する？

データベースを正規化しすぎると、検索時にたくさんの表を結合する必要が出てくることから処理速度が低下すると長年言われてきました。しかし非正規化による結合は処理速度の低下に必ずしも影響しないということを示す実証実験の結果も公開されています。